package com.jie.util;

import com.alibaba.fastjson.JSON;
import com.jie.entity.Agency;
import org.apache.poi.hssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

/**
 * Created by 12904 on 2018/8/10.
 * 导出excel表格工具类
 */
public class ExportExcelUtil
{

    /**
     * exportExcel 导出方法
     * @param data  需要导出的数据
     * @param url  导出的路径
     * @throws IOException
     */
    public static void exportExcel(String data,String url) throws IOException
    {
        List<Agency> list = JSON.parseArray(data,Agency.class);    //将json格式数据转化为list集合

        // 创建一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建一个工作表
        HSSFSheet sheet = workbook.createSheet("净水器");
        // 添加表头行
        HSSFRow hssfRow = sheet.createRow(0);
        // 设置单元格格式居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();

            // 添加表头内容
        HSSFCell headCell = hssfRow.createCell(0);
        headCell.setCellValue("Id");
        headCell.setCellStyle(cellStyle);

        headCell = hssfRow.createCell(1);
        headCell.setCellValue("用户名");
        headCell.setCellStyle(cellStyle);

        headCell = hssfRow.createCell(2);
        headCell.setCellValue("手机号码");
        headCell.setCellStyle(cellStyle);

        headCell = hssfRow.createCell(3);
        headCell.setCellValue("省");
        headCell.setCellStyle(cellStyle);

        headCell = hssfRow.createCell(4);
        headCell.setCellValue("市");
        headCell.setCellStyle(cellStyle);

        headCell = hssfRow.createCell(5);
        headCell.setCellValue("县");
        headCell.setCellStyle(cellStyle);

        headCell = hssfRow.createCell(6);
        headCell.setCellValue("地址");
        headCell.setCellStyle(cellStyle);

        headCell = hssfRow.createCell(7);
        headCell.setCellValue("性别");
        headCell.setCellStyle(cellStyle);

        headCell = hssfRow.createCell(8);
        headCell.setCellValue("加入时间");
        headCell.setCellStyle(cellStyle);

        headCell = hssfRow.createCell(9);
        headCell.setCellValue("邮箱");
        headCell.setCellStyle(cellStyle);


        // 添加数据内容
        for (int i = 0; i < list.size(); i++) {
            hssfRow = sheet.createRow((int) i + 1);
            Agency agency = list.get(i);

            // 创建单元格，并设置值
            HSSFCell cell = hssfRow.createCell(0);
            cell.setCellValue(agency.getUid());
            cell.setCellStyle(cellStyle);

            cell = hssfRow.createCell(1);
            cell.setCellValue(agency.getUlist().get(0).getUname());
            cell.setCellStyle(cellStyle);

            cell = hssfRow.createCell(2);
            cell.setCellValue(agency.getUlist().get(0).getUphone());
            cell.setCellStyle(cellStyle);

            cell = hssfRow.createCell(3);
            cell.setCellValue(agency.getRlist().get(0).getAreaName());
            cell.setCellStyle(cellStyle);

            cell = hssfRow.createCell(4);
            cell.setCellValue(agency.getRlist().get(1).getAreaName());
            cell.setCellStyle(cellStyle);

            cell = hssfRow.createCell(5);
            cell.setCellValue(agency.getRlist().get(2).getAreaName());
            cell.setCellStyle(cellStyle);

            cell = hssfRow.createCell(6);
            cell.setCellValue(agency.getUlist().get(0).getUaddress());
            cell.setCellStyle(cellStyle);

            cell = hssfRow.createCell(7);
            cell.setCellValue(agency.getUlist().get(0).getUsex());
            cell.setCellStyle(cellStyle);

            cell = hssfRow.createCell(8);
            cell.setCellValue(agency.getUlist().get(0).getCreateTime());
            cell.setCellStyle(cellStyle);

            cell = hssfRow.createCell(9);
            cell.setCellValue(agency.getUlist().get(0).getUemail());
            cell.setCellStyle(cellStyle);
        }

        // 保存Excel文件
        try {
            OutputStream outputStream = new FileOutputStream(url);
            workbook.write(outputStream);
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}
